// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MsSqlSpatial
{
	public static class SpatialRefSysTable
	{
	
		internal static bool CheckSRIDExists(SqlCommand command, int srid)
		{
			if (srid == -1)
				return true;
			return DatabaseHelper.CheckQueryExists(command, 
@"SELECT [SRID] FROM [ST].[SPATIAL_REF_SYS] WHERE [SRID] = @p0"
, srid
);
		}
		
		internal static bool IsValidSRID(int srid)
		{
			if (srid == -1)
				return true;
			using (SqlConnection connection = DatabaseHelper.ContextConnection)
			{
				connection.Open();
				using (SqlCommand command = connection.CreateCommand())
				{
					return SpatialRefSysTable.CheckSRIDExists(command, srid);
				}
			}
		}
		
		internal static string GetSRText(int srid)
		{
			if (srid == -1)
				return string.Empty;
			using (SqlConnection connection = DatabaseHelper.ContextConnection)
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand())
				{
					command.Connection = connection;
					DatabaseHelper.BuildCommand(command, 
@"SELECT [SRTEXT] FROM [ST].[SPATIAL_REF_SYS] WHERE [SRID] = @p0"
, srid
);
					return (string)command.ExecuteScalar();
				}
			}
		}
	
		internal static void ExecuteCreate(SqlCommand command)
		{
			if (!DatabaseHelper.CheckMetaTableExists(command, "SPATIAL_REF_SYS"))
			{
				// Create table
				command.CommandText =
@"CREATE TABLE [ST].[SPATIAL_REF_SYS](
	[SRID] [int] NOT NULL,
	[AUTH_NAME] [varchar](256) NULL,
	[AUTH_SRID] [int] NULL,
	[SRTEXT] [varchar](2048) NULL,
PRIMARY KEY CLUSTERED ([SRID] ASC)
WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF)
)";
				command.ExecuteNonQuery();

				// Load data
				ExecutePopulate(command);

				// Create trigger
				ExecuteCreateRefreshTransfromCacheTrigger(command);

			}
		}

		internal static void ExecuteBackup(SqlCommand command)
		{
			if (DatabaseHelper.CheckMetaTableExists(command, "SPATIAL_REF_SYS"))
			{
				string backupTableName = "ST_SPATIAL_REF_SYS_BACKUP_" + DateTime.Now.ToString("yyyyMMdd_HHmm");
				command.CommandText = "SELECT * INTO " + backupTableName + " FROM [ST].[SPATIAL_REF_SYS]";
				command.ExecuteNonQuery();
			}
		}

		public static void ExecuteCreateRefreshTransfromCacheTrigger(SqlCommand command)
		{
			if (DatabaseHelper.CheckMetaTableExists(command, "SPATIAL_REF_SYS"))
			{
				Type type = typeof(UserDefinedFunctions);
				DatabaseHelper.BuildCommand(command, string.Format(
@"CREATE TRIGGER [{0}].[{1}] 
ON [{0}].[{2}]
WITH EXECUTE AS CALLER 
FOR UPDATE, DELETE 
AS EXTERNAL NAME [{3}].[{4}{5}].[{1}]"
, "ST"
, "RefreshTransformCacheTrigger"
, "SPATIAL_REF_SYS"
, type.Assembly.GetName().Name
, (type.Namespace == null ? "" : type.Namespace + ".")
, type.Name
));
				command.ExecuteNonQuery();
			}
		}

		public static void ExecuteDropRefreshTransfromCacheTrigger(SqlCommand command)
		{
			string schemaName = "ST";
			string triggerName = "RefreshTransformCacheTrigger";
			if (DatabaseHelper.CheckTriggerExists(command, schemaName, triggerName))
			{
				DatabaseHelper.BuildCommand(command, string.Format(
@"DROP TRIGGER [{0}].[{1}]"
, schemaName
, triggerName
));
				command.ExecuteNonQuery();
			}
		}

		// Based on a SharpMap Unit Test (WKTCoordSysParserTests.cs)
		internal static void ExecutePopulate(SqlCommand command)
		{
			DatabaseHelper.BuildCommand(command, 
@"INSERT INTO [ST].[SPATIAL_REF_SYS]
VALUES (@SRID,'EPSG',@SRID,@SRTEXT)"
);
			command.Parameters.Add("@SRID", SqlDbType.Int, -1);
			command.Parameters.Add("@SRTEXT", SqlDbType.VarChar, -1);
			
			command.Prepare();

			Stream resource = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("MsSqlSpatial.SRID.csv");
			using (StreamReader sr = new StreamReader(resource))
			{
				while (!sr.EndOfStream)
				{
					string line = sr.ReadLine();
					int split = line.IndexOf(';');
					if (split > -1)
					{
						string srid = line.Substring(0, split);
						string wkt = line.Substring(split + 1);
						command.Parameters["@SRID"].Value = int.Parse(srid);
						command.Parameters["@SRTEXT"].Value = wkt;
						command.ExecuteNonQuery();
					}
				}
			}
			command.Parameters.Clear();
		}
	
	}
}
